﻿using STHPMS1.DAL.D_Core;
using System.Data;
using ServiceStack.OrmLite;
using STHPMS1.ICore.I_Entity.Stmpms1;
using STHPMS1.Common.C_Entity.Stmpms1;
using STHPMS1.ICore.I_DAL.Repository;
using System.Collections.Generic;

namespace STHPMS1.DAL.D_Repository
{
    public class E_EvaluRepository : DalBase, IE_EvaluRepository
    {


        //public List<string> GetRoadnetName()
        //{
        //    IDbConnection conn = Adapter.OpenDbConnection();
        //    return conn.Column<string>("select DISTINCT ROADNETNAME from E_EVALU");
        //}
        //public List<string> GetYear()
        //{
        //    IDbConnection conn = Adapter.OpenDbConnection();
        //    return conn.Column<string>("select DISTINCT SUBSTRING(SVYDATA,0,5) from E_EVALU");
        //}
        //public double? GetLenght(string roadnetname, string year)
        //{
        //    IDbConnection conn = Adapter.OpenDbConnection();
        //    return conn.Scalar<double?>("select SUM(EVALU_LENGTH) from E_EVALU where ROADNETNAME='" + roadnetname + "' and SVYDATA like '%" + year + "%'");
            
        //}

        //public double? GetAvageMQI(string roadnetname, string year)
        //{
        //    IDbConnection conn = Adapter.OpenDbConnection();
        //    //return conn.Scalar<double>("select sum(EVALU_LENGTH*AVAGE_MQI)/SUM(EVALU_LENGTH) from E_EVALU where ADMDIV=" + admdiv + " and SVYDATA='" + svydate + "'");
        //    return conn.Scalar<double?>("select SUM((B_ROADWAY.ENDMP-B_ROADWAY.BEGINMP)*mqi)/sum(B_ROADWAY.ENDMP-B_ROADWAY.BEGINMP) from (select sum(AVAGE_MQI)/COUNT(*) as mqi,E_EVALU.RDWAYID from E_EVALU where E_EVALU.ROADNETNAME='" + roadnetname + "' and E_EVALU.SVYDATA like '%" + year + "%' group by E_EVALU.RDWAYID,E_EVALU.SVYDATA  ) as tab left join B_ROADWAY on tab.RDWAYID= B_ROADWAY.RDWAYID");
        //}

        //public double? GetLenght(string roadnetname, string year, string searchvalue)
        //{
        //    IDbConnection conn = Adapter.OpenDbConnection();
            
        //    //return conn.Scalar<double>("select sum(ENDMP-BEGINMP) from E_EVALU left join  B_ROADWAY on E_EVALU.RDWAYID=B_ROADWAY.RDWAYID where E_EVALU.ADMDIV="+admdiv+" and SVYDATA='"+svydate+"'  "+searchvalue+"");

        //    return conn.Scalar<double?>("select SUM(lenght) from (select  sum(ENDMP-BEGINMP)/COUNT(*) as lenght  from E_EVALU left join  B_ROADWAY on E_EVALU.RDWAYID=B_ROADWAY.RDWAYID where E_EVALU.ROADNETNAME='" + roadnetname + "' and SVYDATA like '%" + year + "%' " + searchvalue + " group by E_EVALU.RDWAYID,E_EVALU.SVYDATA) as tab");
        //}




        /// <summary>
        /// 获取评定长度
        /// </summary>
        /// <param name="inspecid">评定名称</param>
        /// <param name="rdwayid">路线ID</param>
        /// <param name="searchvalues">查询条件</param>
        /// <returns></returns>
        public double? GetLenght(string inspecid, string rdwayid, string searchvalues)
        {
            //IDbConnection conn = Adapter.OpenDbConnection();
            //return conn.Scalar<double>("select sum(ENDMP-BEGINMP) as Lenght from B_UNIT where UNIT in(select UNIT from E_ROADWAYDETAIL where INSPECID='"+inspecid+"' and RDWAYID='"+rdwayid+"' and DIRECTION_DICTID="+d+" "+searchvalues+")");

            //IDbConnection conn = Adapter.OpenDbConnection();
            //return conn.Scalar<double?>("select sum(B_UNIT.ENDMP-B_UNIT.BEGINMP) as Lenght from E_ROADWAYDETAIL "
            //                            + "left join B_UNIT on E_ROADWAYDETAIL.UNIT=B_UNIT.UNIT  "
            //                            + "left join B_INVNTORY_UNIT on E_ROADWAYDETAIL.UNIT=B_INVNTORY_UNIT.UNIT "
            //                            + "left join B_INVNTORY  on B_INVNTORY_UNIT.SEGID=B_INVNTORY.SEGID "
            //                            + "where INSPECID='" + inspecid + "' and B_INVNTORY.RDWAYID='" + rdwayid + "'  and E_ROADWAYDETAIL.DIRECTION_DICTID=" + d + " " + searchvalues + "");


            IDbConnection conn = Adapter.OpenDbConnection();
            string strsql = "select sum(B_UNIT.ENDMP-B_UNIT.BEGINMP) as Lenght from E_ROADWAYDETAIL "
                                        + "left join B_UNIT on E_ROADWAYDETAIL.UNIT=B_UNIT.UNIT  "
                                        + "left join B_INVNTORY_UNIT on E_ROADWAYDETAIL.UNIT=B_INVNTORY_UNIT.UNIT "
                                        + "left join B_INVNTORY  on B_INVNTORY_UNIT.SEGID=B_INVNTORY.SEGID "
                                        + "where INSPECID='" + inspecid + "' and B_INVNTORY.RDWAYID='" + rdwayid + "' " + searchvalues + "";
            return conn.Scalar<double?>(strsql);
        }

        /// <summary>
        /// 获取MQI的平均值
        /// </summary>
        /// <param name="inspecid">评定名称</param>
        /// <param name="rdwayid">路线ID</param>
        /// <param name="searchvalues">查询条件</param>
        /// <returns></returns>
        public double? GetAverage(string inspecid, string rdwayid, string searchvalues)
        {

            //MQI求平均值：MQI之和/数量
            //return conn.Scalar<double>("select sum(" + searchvalues + ")/COUNT(*) from E_ROADWAYDETAIL where INSPECID='" + inspecid + "' and RDWAYID='" + rdwayid + "' and DIRECTION_DICTID=" + d + "");


            //MQI求平均值：(长度*MQI+长度*MQI)/总长度  每个单元的长度乘以MQI / 各个单元长度之和
            //IDbConnection conn = Adapter.OpenDbConnection();
            //return conn.Scalar<double>("select sum((ENDMP-BEGINMP)*"+searchvalues+")/sum(ENDMP-BEGINMP) from E_ROADWAYDETAIL left join B_UNIT on E_ROADWAYDETAIL.UNIT=B_UNIT.UNIT where INSPECID='"+inspecid+"' and RDWAYID='"+rdwayid+"' and DIRECTION_DICTID="+d+"");



            //IDbConnection conn = Adapter.OpenDbConnection();

            //return conn.Scalar<double?>("select sum((B_UNIT.ENDMP-B_UNIT.BEGINMP)*MQI)/ SUM(B_UNIT.ENDMP-B_UNIT.BEGINMP) as Lenght from E_ROADWAYDETAIL "
            //                            + "left join B_UNIT on E_ROADWAYDETAIL.UNIT=B_UNIT.UNIT  "
            //                            + "left join B_INVNTORY_UNIT on E_ROADWAYDETAIL.UNIT=B_INVNTORY_UNIT.UNIT "
            //                            + "left join B_INVNTORY  on B_INVNTORY_UNIT.SEGID=B_INVNTORY.SEGID "
            //                            + "where INSPECID='" + inspecid + "' and B_INVNTORY.RDWAYID='" + rdwayid + "'  and E_ROADWAYDETAIL.DIRECTION_DICTID=" + d + " " + searchvalues + "");

            IDbConnection conn = Adapter.OpenDbConnection();
            string strsql = "select sum((B_UNIT.ENDMP-B_UNIT.BEGINMP)*MQI)/ SUM(B_UNIT.ENDMP-B_UNIT.BEGINMP) as Lenght from E_ROADWAYDETAIL "
                                        + "left join B_UNIT on E_ROADWAYDETAIL.UNIT=B_UNIT.UNIT  "
                                        + "left join B_INVNTORY_UNIT on E_ROADWAYDETAIL.UNIT=B_INVNTORY_UNIT.UNIT "
                                        + "left join B_INVNTORY  on B_INVNTORY_UNIT.SEGID=B_INVNTORY.SEGID "
                                        + "where INSPECID='" + inspecid + "' and B_INVNTORY.RDWAYID='" + rdwayid + "'  " + searchvalues + "";
            return conn.Scalar<double?>(strsql);


        }

    }
}
